﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Transactions;

namespace DataAccessLayer
{
    public class DisbursementListDAL
    {
        StationeryInventoryEntities1 cntx = new StationeryInventoryEntities1();
        public List<CollectionPoint> getCollectionPointNames()
        {
            List<CollectionPoint> collectionPointNames = (from cp in cntx.CollectionPoints
                                                          select cp).ToList();
            return collectionPointNames;
        }

        public List<Category> getCategories()
        {
            List<Category> categories = (from c in cntx.Categories
                                         select c).ToList();
            return categories;
        }

        public List<Stationery> getStationeryNames(int CategoryId)
        {
            List<Stationery> stat = (from s in cntx.Stationeries
                                     where s.CategoryID == CategoryId
                                     select s).ToList();
            return stat;
        }

        public IEnumerable<Object> getDeptDetails(int colPointId)
        {
            var deptDetails = (from d in cntx.Departments
                               join c in cntx.CollectionPoints
                               on d.CollectionPointID equals c.CollectionPointID
                               join dr in cntx.DeptRequisitions
                               on d.DeptID equals dr.DeptID
                               where dr.DisbursedBy == null && dr.RetrievedDate != null &&
                               d.CollectionPointID == colPointId
                               select new { d.DeptName, d.DeptID }).ToList();
            return deptDetails;
        }

        public IEnumerable<Object> getDisburementDetailsList(String DeptCode)
        {
            DeptRequisition dr = (from d1 in cntx.DeptRequisitions
                                  where d1.DeptID == DeptCode
                                  orderby d1.RetrievedDate descending
                                  select d1).FirstOrDefault();

            var ItemsAndQty = (from dd in cntx.DeptRequisitionDetails
                               where dd.DeptReqID == dr.DeptReqID
                               select new { dd.Stationery.ItemName, dd.ActualQty }).ToList();

            return ItemsAndQty;
        }

        public void updDisbursementDetails(String DeptCode,String empId)
        {
            DeptRequisition dr = (from d1 in cntx.DeptRequisitions
                                  where d1.DeptID == DeptCode
                                  orderby d1.RetrievedDate descending
                                  select d1).FirstOrDefault();
            List<DeptRequisitionDetail> ItemsAndQty2 = (from dd in cntx.DeptRequisitionDetails
                                                        where dd.DeptReqID == dr.DeptReqID
                                                        select dd).ToList();
            foreach (DeptRequisitionDetail dpd in ItemsAndQty2)
            {
                Stationery s = (from s1 in cntx.Stationeries
                                where s1.ItemCode == dpd.ItemCode
                                select s1).FirstOrDefault();

                //Updating the Available Quantity in Stationery
                s.AvailableQty = s.AvailableQty - (int)dpd.ActualQty;

                //Creating a new record for Stock History
                StockHistory sh = new StockHistory();
                sh.ItemCode = dpd.ItemCode;
                sh.Date = DateTime.Now;
                sh.Description = dr.Department.DeptName;    //TESTstock     //"Retrieval for Stock Requisition for department";
                sh.AdjustedQty = dpd.ActualQty * (-1);      //TESTstock
                sh.Balance = s.AvailableQty;                //TESTstock
                cntx.StockHistories.Add(sh);

                //Updating the Disbursed By in DeptRequisition
                DeptRequisition d = (from d1 in cntx.DeptRequisitions
                                     where d1.DeptReqID == dpd.DeptReqID
                                     select d1).FirstOrDefault();
                d.DisbursedBy = Convert.ToInt32(empId);
            }
            cntx.SaveChanges();
        }

        public IEnumerable<Object> getDisbursementDeptQtybyItem(int ColPointId, String iCode)
        {
            var result = (from d in cntx.Departments
                          join c in cntx.CollectionPoints
                          on d.CollectionPointID equals c.CollectionPointID
                          join dr in cntx.DeptRequisitions
                          on d.DeptID equals dr.DeptID
                          join drd in cntx.DeptRequisitionDetails
                          on dr.DeptReqID equals drd.DeptReqID
                          where drd.ItemCode == iCode &&
                          dr.DisbursedBy == null && dr.RetrievedDate != null &&
                          d.CollectionPointID == ColPointId
                          select new { drd.DeptReqDetailID, d.DeptName, drd.Stationery.ItemName, drd.Stationery.AvailableQty, drd.ActualQty }).ToList();
            return result;
        }

        public void updAdjustmentandStockHistory(int quantity, string itemName)
        {
            Stationery stationeryAvailable = (from s in cntx.Stationeries
                                              where s.ItemName == itemName
                                              select s).FirstOrDefault();
            //Updating Available Quantity in Stationery
            stationeryAvailable.AvailableQty = Convert.ToInt32(stationeryAvailable.AvailableQty) - quantity;

            //Creating a new record in StockHistory
            StockHistory sh = new StockHistory();
            sh.ItemCode = stationeryAvailable.ItemCode;
            sh.Date = DateTime.Now;
            sh.Description = "Lost/Damaged during disbursement at/near collection point";
            sh.AdjustedQty = quantity;
            sh.Balance = Convert.ToInt32(Convert.ToInt32(stationeryAvailable.AvailableQty) - quantity);

            cntx.StockHistories.Add(sh);
            cntx.SaveChanges();

            StockHistory sh1 = (from sh12 in cntx.StockHistories
                                where sh12.ItemCode == sh.Stationery.ItemCode
                                orderby sh12.StockHistoryID descending
                                select sh12).First();

            //Creating a new record in Adjustment and AdjustmentDetail
            Adjustment ad = new Adjustment();
            ad.RequestedBy = 1;
            ad.RequestedDate = DateTime.Now;
            ad.Status = "Pending";

            cntx.Adjustments.Add(ad);
            cntx.SaveChanges();

            SupplierDetail sd = (from sd1 in cntx.SupplierDetails
                                 where sd1.ItemCode == sd1.Stationery.ItemCode && sd1.Rank == 1
                                 select sd1).FirstOrDefault();

            AdjustmentDetail ajd = new AdjustmentDetail();
            ajd.AdjVoucherID = ad.AdjVoucherID;
            ajd.ItemCode = stationeryAvailable.ItemCode;
            ajd.Quantity = quantity;
            ajd.Reason = "Lost/Damaged during disbursement at/near collection point";
            ajd.SupplierID = sd.SupplierID;
            ajd.StockHistoryID = sh1.StockHistoryID;

            cntx.AdjustmentDetails.Add(ajd);

            cntx.SaveChanges();
        }

        public void updateActualQty(int DepReqId, int ActualQty)
        {
            var drd = (from d in cntx.DeptRequisitionDetails
                       where d.DeptReqDetailID == DepReqId
                       select d).First();

            drd.ActualQty = ActualQty;
            cntx.SaveChanges();
        }

        public DateTime getCollectionPointTime(string colPointName)
        {
            //comment
            //CollectionPoint cp = (from c in cntx.CollectionPoints
            //                      where c.CollectionPointName == colPointName
            //                      select c).FirstOrDefault();

            CollectionPointDetail cpd = (from c in cntx.CollectionPointDetails
                                         where c.CollectionPoint.CollectionPointName == colPointName
                                         orderby c.CollectionPointDetailID descending
                                         select c).FirstOrDefault();

            DateTime cTime =Convert.ToDateTime(cpd.CollectionTime);

            return cTime;
        }

        public string getDeptRepName(string DeptName)
        {
            Department d = (from d1 in cntx.Departments
                            where d1.DeptName == DeptName
                            select d1).FirstOrDefault();
            Employee e = (from e1 in cntx.Employees
                          where e1.EmployeeID == d.RepresentativeID
                          select e1).FirstOrDefault();
            string reName = e.EmployeeName.ToString();
            return reName;
        }

        public DateTime getCollectionDate(string DeptName)
        {
            Department d = (from d1 in cntx.Departments
                            where d1.DeptName == DeptName
                            select d1).FirstOrDefault();

            DeptRequisition dr = (from dr1 in cntx.DeptRequisitions
                                  where dr1.DeptID == d.DeptID &&
                                  dr1.DisbursedBy == null && dr1.RetrievedBy != null && dr1.RetrievedDate != null
                                  select dr1).FirstOrDefault();

            CollectionPointDetail cpd = (from cpd1 in cntx.CollectionPointDetails
                                         where cpd1.CollectionPointDetailID == dr.CollectionPointDetailID
                                         select cpd1).FirstOrDefault();

            DateTime collectionDate = Convert.ToDateTime(cpd.CollectionDate);
            return collectionDate;
        }

        public void updateDeptReqDetActualQty(List<int> lstDeptReqDetId, List<int> lstAllocatedQty)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                for (int i = 0; i < lstDeptReqDetId.Count; i++)
                {
                    int num = lstDeptReqDetId[i];
                    DeptRequisitionDetail deptDet = cntx.DeptRequisitionDetails.FirstOrDefault(x => x.DeptReqDetailID == num);

                    if (lstAllocatedQty[i] != deptDet.ActualQty)
                    {
                        OutstandingQty outQty = new OutstandingQty();
                        outQty.DeptID = deptDet.DeptRequisition.DeptID;
                        outQty.ItemCode = deptDet.ItemCode;
                        outQty.OutstandingQty1 = deptDet.ActualQty - lstAllocatedQty[i];
                        outQty.Status = "Outstanding";
                        cntx.OutstandingQties.Add(outQty);
                        cntx.SaveChanges();
                    }

                    deptDet.ActualQty = lstAllocatedQty[i];
                    cntx.SaveChanges();
                }
                ts.Complete();
            }


        }

    }
}
